use calamine::{open_workbook_auto_from_rs, Data, DataType, Range, Reader};
use shq_common::prelude::clickhouse::exec_sqls;
use shq_common::prelude::{alpha_to_index, excel_cell_to_tuple, get_work_path, index_to_alpha};
use std::{fs, vec};
use std::{io::Cursor, path::Path};

use crate::definition::SqlList;
use crate::{
    config::{
        self,
        definition::{CellValue, Config, Sheet, Table, Tables},
    },
    definition::{Column, SaveMode},
};

use super::category::change_category_to_db;
use super::table_data::{has_in_db, CellData, ColumnDatas, TableDatas};
pub(crate) struct ExcelReader {
    config: &'static Config,
}
impl ExcelReader {
    pub(crate) fn new(conf_name: &str) -> anyhow::Result<Self> {
        let config = config::get_excel_rept_config(conf_name)
            .ok_or(anyhow::anyhow!("配置文件{conf_name}未找到"))?;
        Ok(ExcelReader { config })
    }
    pub(crate) async fn read_excel_by_bytes(
        &self,
        bytes: Vec<u8>,
        params: &[(&str, &str)],
    ) -> anyhow::Result<()> {
        let cursor = Cursor::new(bytes);
        let mut work_book = open_workbook_auto_from_rs(cursor)?;
        let mut get_sheet_datas = |sheet: &Sheet| -> anyhow::Result<Vec<(String, Range<Data>)>> {
            let mut result: Vec<(String, Range<Data>)> = Vec::new();
            let all_sheet_names = work_book.sheet_names();
            match &sheet.sheet_name {
                config::definition::SheetName::Name(sheet_name) => {
                    let datas = work_book
                        .worksheet_range(sheet_name)
                        .or_else(|_| Err(anyhow::anyhow!("Sheet {} 不存在", sheet_name)))?;
                    result.push((sheet_name.to_owned(), datas));
                }
                config::definition::SheetName::Index(index) => {
                    let sheet_name = all_sheet_names
                        .get(*index as usize - 1)
                        .ok_or_else(|| anyhow::anyhow!("Sheet {} 不存在", index))?;
                    let datas = work_book
                        .worksheet_range(sheet_name)
                        .or_else(|_| Err(anyhow::anyhow!("Sheet {} 不存在", sheet_name)))?;
                    result.push((sheet_name.to_owned(), datas));
                }
                config::definition::SheetName::Multi(items) => {
                    for index in items.iter() {
                        let sheet_name = all_sheet_names
                            .get(*index as usize - 1)
                            .ok_or_else(|| anyhow::anyhow!("Sheet {} 不存在", index))?;
                        let datas = work_book
                            .worksheet_range(sheet_name)
                            .or_else(|_| Err(anyhow::anyhow!("Sheet {} 不存在", index)))?;
                        result.push((sheet_name.to_owned(), datas));
                    }
                }
                config::definition::SheetName::Range((start, end)) => {
                    let start_idx = all_sheet_names
                        .iter()
                        .enumerate()
                        .position(|(i, _)| i + 1 == *start as usize)
                        .ok_or(anyhow::anyhow!("Sheet {} 不存在", start))?;
                    let end_idx = all_sheet_names
                        .iter()
                        .enumerate()
                        .position(|(i, _)| i + 1 == *end as usize)
                        .ok_or(anyhow::anyhow!("Sheet {} 不存在", end))?;
                    for i in start_idx..=end_idx {
                        let sheet_name = &all_sheet_names[i];
                        let datas = work_book
                            .worksheet_range(sheet_name)
                            .or_else(|_| Err(anyhow::anyhow!("Sheet {} 不存在", sheet_name)))?;
                        result.push((sheet_name.to_owned(), datas));
                    }
                }
                config::definition::SheetName::All => {
                    for sheet_name in all_sheet_names.into_iter() {
                        let datas = work_book
                            .worksheet_range(&sheet_name)
                            .or_else(|_| Err(anyhow::anyhow!("Sheet {} 不存在", sheet_name)))?;
                        result.push((sheet_name, datas));
                    }
                }
            }
            Ok(result)
        };
        let mut sheet_datas: Vec<(String, &Table, Range<Data>)> = vec![];
        match &self.config.sheets {
            config::definition::Sheets::Single(sheet) => match &sheet.tables {
                Tables::Single(table) => {
                    let datas = get_sheet_datas(&sheet)?;
                    for (sname, datas) in datas.into_iter() {
                        sheet_datas.push((sname, table, datas));
                    }
                }
                Tables::Multi(tables) => {
                    for table in tables.iter() {
                        let datas = get_sheet_datas(&sheet)?;
                        for (sname, datas) in datas.into_iter() {
                            sheet_datas.push((sname, table, datas));
                        }
                    }
                }
            },
            config::definition::Sheets::Multi(sheets) => {
                for sheet in sheets.iter() {
                    match &sheet.tables {
                        Tables::Single(table) => {
                            let datas = get_sheet_datas(&sheet)?;
                            for (sname, datas) in datas.into_iter() {
                                sheet_datas.push((sname, table, datas));
                            }
                        }
                        Tables::Multi(tables) => {
                            for table in tables.iter() {
                                let datas = get_sheet_datas(&sheet)?;
                                for (sname, datas) in datas.into_iter() {
                                    sheet_datas.push((sname, table, datas));
                                }
                            }
                        }
                    }
                }
            }
        }
        let mut tds: TableDatas = TableDatas::new();
        for (sheet_name, table, datas) in sheet_datas.iter() {
            let db_table = if let Some(dt) = &table.db_table {
                Ok(dt)
            } else {
                let res = if let Some(dt) = &self.config.db_table {
                    Ok(dt)
                } else {
                    Err(anyhow::anyhow!(
                        "sheet:{sheet_name}的tables中db_table未定义"
                    ))
                };
                res
            }?;
            let kvs = get_key_values(&table.columns, params, &sheet_name)?;
            if db_table.save_mode == SaveMode::IgnoreIfExists {
                let has = has_in_db(&db_table.table_name, &kvs).await?;
                if has {
                    continue;
                }
            } else if db_table.save_mode == SaveMode::ErrorIfExists {
                let has = has_in_db(&db_table.table_name, &kvs).await?;
                if has {
                    anyhow::bail!("数据已存在了，不能重复插入");
                }
            }
            let row_range = if let Some(r) = &table.row_range {
                let s = r
                    .start
                    .to_u32(datas, 0, 1)
                    .or_else(|err| Err(anyhow::anyhow!("sheet:{sheet_name};{err}")))?;
                let e = r
                    .end
                    .to_u32(datas, s, -1)
                    .or_else(|err| Err(anyhow::anyhow!("sheet:{sheet_name};{err}")))?;
                let skips = r.skips.as_ref().map(|skips| {
                    skips
                        .iter()
                        .map(|rno| rno.to_u32(datas, s, 0).unwrap_or(0))
                        .collect::<Vec<u32>>()
                });
                Some((s, e, skips))
            } else {
                None
            };
            let col_datas =
                read_excel_from_table(&datas, table, params, sheet_name, row_range.clone())
                    .await
                    .or_else(|err| Err(anyhow::anyhow!("sheet:{sheet_name};{err}")))?;
            let start_row = row_range.map(|r| r.0);
            tds.add_table_data(sheet_name.as_str(), table, db_table, col_datas, start_row)
                .await?;
        }
        tds.save().await?;
        if let Some(sql_list) = self.config.import_after_sql.as_ref() {
            let pnames: Vec<String> = params.iter().map(|(k, _v)| format!("{{{}}}", k)).collect();
            let pnames: Vec<&str> = pnames.iter().map(|v| v.as_str()).collect();
            let pvals: Vec<&str> = params.iter().map(|(_k, v)| *v).collect();
            match sql_list {
                SqlList::Sql(s) => exec_sqls(s, &pnames, &pvals).await?,
                SqlList::Sqls(items) => {
                    for sql in items.iter() {
                        exec_sqls(sql, &pnames, &pvals).await?;
                    }
                }
                SqlList::Path(fname) => {
                    let fpath = format!("{}/sqls/{}", get_work_path(), fname);
                    let path = Path::new(fpath.as_str());
                    let sqls = fs::read_to_string(path)
                        .map_err(|err| anyhow::anyhow!("读取文件{fname}失败:{err}"))?;
                    exec_sqls(&sqls, &pnames, &pvals).await?;
                }
            }
        }
        Ok(())
    }
    pub(crate) async fn read_excel_by_file_path(
        &self,
        file_path: &str,
        params: &[(&str, &str)],
    ) -> anyhow::Result<()> {
        let path = Path::new(file_path);
        let bytes =
            fs::read(path).map_err(|err| anyhow::anyhow!("读取文件{file_path}失败:{err}"))?;
        self.read_excel_by_bytes(bytes, params).await
    }
}

async fn read_excel_from_table<'a>(
    datas: &'a Range<Data>,
    table: &'a Table,
    params: &'a [(&'a str, &'a str)],
    sheet_name: &'a str,
    row_range: Option<(u32, u32, Option<Vec<u32>>)>,
) -> anyhow::Result<Vec<ColumnDatas<'a>>> {
    let mut result: Vec<ColumnDatas> = Vec::new();
    let mut seek_start_idx = 0usize;
    if let Some(category) = &table.category {
        let cgs = change_category_to_db(datas, category).await?;
        let row_range = table
            .row_range
            .as_ref()
            .ok_or(anyhow::anyhow!("Category表格必须定义row_range"))?;
        let start = row_range
            .start
            .to_u32(datas, 0, 0)
            .or_else(|err| Err(anyhow::anyhow!("sheet:{sheet_name};{err}")))?;
        let end = row_range
            .end
            .to_u32(datas, 0, 0)
            .or_else(|err| Err(anyhow::anyhow!("sheet:{sheet_name};{err}")))?;
        for col in table.columns.iter() {
            let cell_value = &col.cell_value;
            for row_num in start..=end {
                let i = row_num - 1;
                if let CellValue::CrossID = cell_value {
                    let mut cids: Vec<CellData<'a>> = vec![];
                    for (_col_idx, cross_id) in cgs.iter() {
                        cids.push(CellData::Int(*cross_id as i64));
                    }
                    result.push(ColumnDatas::new(col.col_name.as_str(), cids));
                } else if let CellValue::CrossValue = cell_value {
                    let mut cdatas: Vec<CellData<'a>> = vec![];
                    for (col_idx, _cross_id) in cgs.iter() {
                        let cval = datas.get_value((i, *col_idx as u32));
                        if !col.can_none.unwrap_or_default() && cval.is_none() {
                            anyhow::bail!(
                                "没有找到单元格{}{},请检查导入文件格式",
                                index_to_alpha(*col_idx + 1),
                                i + 1
                            );
                        }
                        cdatas.push(CellData::from_data(cval));
                    }
                    result.push(ColumnDatas::new(col.col_name.as_str(), cdatas));
                } else {
                    let mut cdatas: Vec<CellData<'a>> = vec![];
                    let (cell_val, col_idx, _) = get_cell_value(
                        col.col_desc.as_str(),
                        &col.cell_value,
                        datas,
                        params,
                        sheet_name,
                        col.can_none,
                        &mut seek_start_idx,
                    )?;
                    for (_cidx, _cross_id) in cgs.iter() {
                        if let Some(col_idx) = col_idx {
                            let cval = datas.get_value((i, col_idx));
                            if !col.can_none.unwrap_or_default() && cval.is_none() {
                                anyhow::bail!(
                                    "没有找到单元格{}{},请检查导入文件格式",
                                    index_to_alpha(col_idx + 1),
                                    i + 1
                                );
                            }
                            cdatas.push(CellData::from_data(cval));
                        } else {
                            cdatas.push(cell_val.clone());
                        }
                    }
                    result.push(ColumnDatas::new(col.col_name.as_str(), cdatas));
                }
            }
        }
    } else {
        for col in table.columns.iter() {
            let (cell_val, col_idx, is_row_no) = get_cell_value(
                col.col_desc.as_str(),
                &col.cell_value,
                datas,
                params,
                sheet_name,
                col.can_none,
                &mut seek_start_idx,
            )?;
            let col_datas = get_col_datas(
                cell_val,
                col_idx,
                datas,
                row_range.clone(),
                col.can_none,
                is_row_no,
            )?;
            result.push(ColumnDatas::new(col.col_name.as_str(), col_datas));
        }
    };
    Ok(result)
}
fn get_key_values<'a>(
    cols: &'a [Column],
    params: &[(&'a str, &'a str)],
    sheet_name: &'a str,
) -> anyhow::Result<Vec<(&'a str, &'a str)>> {
    let mut result: Vec<(&str, &str)> = Vec::new();
    for col in cols.iter() {
        match &col.cell_value {
            CellValue::Param(p) => {
                let pname = &p[1..];
                let param = params
                    .iter()
                    .find(|(k, _)| k == &pname)
                    .ok_or(anyhow::anyhow!("Param:{p} not found"))?;
                result.push((col.col_name.as_str(), param.1));
            }
            CellValue::SheetName => result.push((col.col_name.as_str(), sheet_name)),
            _ => {}
        }
    }
    Ok(result)
}
fn get_col_datas<'a>(
    cell_val: CellData<'a>,
    col_idx: Option<u32>,
    datas: &'a Range<Data>,
    row_range: Option<(u32, u32, Option<Vec<u32>>)>,
    can_none: Option<bool>,
    is_row_no: bool,
) -> anyhow::Result<Vec<CellData<'a>>> {
    if col_idx.is_some() && row_range.is_none() {
        anyhow::bail!("CellValue配置为列，sheet配置中必须定义row_range");
    }
    let mut col_datas: Vec<CellData<'a>> = Vec::new();
    let can_none = can_none.map(|v| v == true).unwrap_or(false);
    if let Some((start_row, end_row, skips)) = row_range {
        for row_num in start_row..=end_row {
            if let Some(skips) = &skips {
                if skips.contains(&(row_num as u32)) {
                    continue; // 跳过指定的行
                }
            }
            let i = row_num - 1;
            if let Some(col_idx) = col_idx {
                let cval = datas.get_value((i, col_idx));
                if !can_none && cval.is_none() {
                    anyhow::bail!(
                        "没有找到单元格{}{},请检查导入文件格式",
                        index_to_alpha(col_idx + 1),
                        i + 1
                    );
                }
                col_datas.push(CellData::from_data(cval));
            } else if is_row_no {
                col_datas.push(CellData::Int(row_num as i64));
            } else {
                col_datas.push(cell_val.clone());
            }
        }
    } else {
        col_datas.push(cell_val);
    }
    Ok(col_datas)
}

fn get_cell_value<'a>(
    col_desc: &'a str,
    col_val: &'a CellValue,
    datas: &'a Range<Data>,
    params: &[(&'a str, &'a str)],
    sheet_name: &'a str,
    can_none: Option<bool>,
    seek_start_idx: &mut usize,
) -> anyhow::Result<(CellData<'a>, Option<u32>, bool)> {
    let can_none = can_none.map(|v| v == true).unwrap_or(false);
    let (cell_data, col_idx, is_rowno) = match col_val {
        CellValue::Col(c) => {
            let col_idx = Some(alpha_to_index(c) - 1);
            (CellData::None, col_idx, false)
        }
        CellValue::Cell(c) => {
            let (col_num, row_num) = excel_cell_to_tuple(c.as_str())?;
            let col_idx = col_num - 1;
            let row_idx = row_num - 1;
            let cval = datas.get_value((row_idx, col_idx));
            let cell_data = cval
                .map(|v| CellData::Data(v))
                .ok_or(anyhow::anyhow!("没有找到单元格{c}，请检查导入文件格式"))?;
            (cell_data, None, false)
        }
        CellValue::Param(p) => {
            let param = params
                .iter()
                .find(|(k, _)| k == &&p[1..])
                .ok_or(anyhow::anyhow!("Param:{p} not found"))?;
            (CellData::Str(param.1), None, false)
        }
        CellValue::SheetName => (CellData::Str(sheet_name), None, false),
        CellValue::RowNo => (CellData::None, None, true),
        CellValue::Seek(cv) => {
            let mut col_idx = None;
            let w = datas.get_size().1;
            let start = *seek_start_idx;
            for i in start..w {
                let row = i as u32;
                let cval = datas.get_value((cv - 1, i as u32));
                if let Some(cval) = cval {
                    if let Some(t) = cval.get_string() {
                        let t: String = t.chars().filter(|c| !c.is_whitespace()).collect();
                        if t.as_str() == col_desc || t.contains(col_desc) {
                            col_idx = Some(row);
                            *seek_start_idx = i + 1; // 更新起始索引
                            break;
                        }
                    }
                }
            }
            if !can_none {
                if col_idx.is_none() {
                    anyhow::bail!("在第{}行没有找到值{}", cv, col_desc);
                }
            }
            (CellData::None, col_idx, false)
        }
        CellValue::CrossID | CellValue::CrossValue => {
            anyhow::bail!("CrossID和CrossValue不支持get_cell_value方法")
        }
    };
    Ok((cell_data, col_idx, is_rowno))
}
